package bc.running.forum.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.sql.DataSource;

import bc.running.forum.db.meta.Column;
import bc.running.forum.db.meta.DBSchema;
import bc.running.forum.db.meta.Table;


/**
 * Query the DB 'DatabaseMetaData' and dump the tables structures to XML
 * 
 * @author avishayb
 * 
 */
public class SchemaExtractor {

	public SchemaExtractor(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public DBSchema extract(String catalog,String schemaPattern,String tableNamePattern,String [] types) throws Exception {
		Connection con = dataSource.getConnection();
		DatabaseMetaData metaData = con.getMetaData();
		ResultSet tables = metaData.getTables(catalog, null/*schemaPattern*/, tableNamePattern, types);
		DBSchema schema = new DBSchema();
		List<Table> tablesList = new ArrayList<Table>();
		while (tables.next()) {
			String tableName = tables.getString("TABLE_NAME");
			Table table = new Table();
			table.setName(tableName);
			List<Column> columns = getColumns(metaData, tableName);
			table.setColumns(columns);
			tablesList.add(table);
		}
		schema.setTables(tablesList);
		//dumpToXML(schema);
		return schema;

	}

	private List<Column> getColumns(DatabaseMetaData metaData, String tableName)
			throws Exception {
		Set<String> primaryKeys = getPrimaryKeys(metaData, tableName);
		ResultSet rsColumns = metaData.getColumns(null, null, tableName, null);
		List<Column> result = new ArrayList<Column>();
		while (rsColumns.next()) {
			String name = rsColumns.getString("COLUMN_NAME");
			String dataType = rsColumns.getString("TYPE_NAME");
			String size = rsColumns.getString("COLUMN_SIZE");
			String nullable = rsColumns.getString("IS_NULLABLE");
			Column column = new Column();
			column.setColumnDataType(dataType);
			column.setColumnName(name);
			column.setColumnNullable(nullable);
			column.setColumnSize(size);
			column.setPk(primaryKeys.contains(name));
			result.add(column);
		}
		return result;
	}

	private Set<String> getPrimaryKeys(DatabaseMetaData metaData,
			String tableName) throws Exception {
		Set<String> primaryKeys = new HashSet<String>();
		ResultSet resultSet = metaData.getPrimaryKeys(null, null, tableName);
		while (resultSet.next()) {
		      String pkFieldName = resultSet.getString("COLUMN_NAME");
		      primaryKeys.add(pkFieldName);
		}
		return primaryKeys;

	}

	
	private DataSource dataSource;

}
